import * as Router from 'koa-router';
import * as moment from 'moment';
import * as fs from 'fs';
import consumerRecoderModel from '../model/consumerRecoderModel';
import visitMonthPlanModel from '../model/visitMonthPlanModel';
import dingTalkApi from 'dingtalk-app/dtalk/DingTalkApi';
import dTalkApi from '../core/dTalkApi';
import dTalkApiUtil from 'dingtalk-app/dtalk/dTalkApiUtil';
import storeInfoModel from '../model/storeInfoModel';
import { IRouterContext } from 'koa-router';
import storeRecoderModel from '../model/storeRecoderModel';
import visitMonthPlanService from '../services/visitMonthPlanService';
import syncDataService from '../services/syncDataService';
import formDataService from '../services/formDataService';
import excelExportModel from '../model/excelExportModel';
import emplModel from '../model/empl2Model';
const xlsx = require('node-xlsx');
let Multer = require('koa-multer');
let multer = Multer({});
let router = new Router();
router.prefix('/api/leadexcel');
//(一)导入
//导入门店信息表
router.post('/testStoreInfoExcel', multer.single('file'), async (ctx: IRouterContext, next) => {
    let user = dingTalkApi.decode(ctx.header.authorization)
    let rp: any = ctx.request;
    let excel = xlsx.parse(rp.req.file.buffer);
    let data = excel[0];
    let errList: any = []//错误数据列表
    let list: any[] = []
    //row:行  col:列
    data.data.map(async (row: any, col: any) => {
        if (!errList[col]) {
            errList[col] = {
                errCode: '',
                errName: '',
                comment: 1,
            }
        }
        let bill: any = {
            storeCode: row[0],//门店编码
            storeName: row[1],//门店名称
            store_classification: row[2],//门店类型
            channel: row[3],//门店渠道
            contactName: row[4],//联系人姓名
            phone: row[5],//联系人电话
            consumerCode: row[6],//客户编号
            consumerName: row[7],//客户名称
            storeCity: row[8],//城市名称
            cityType: row[9],//城市级别
            storeAdress: row[10],//门店地址
            iT_Mall_name: row[11],//电脑城名称
            iT_Mall_address: row[12]//电脑城地址
        };
        if (bill.storeCode && bill.storeName) {
            if (col != 0) {
                let a=bill.store_classification;
                let patt1 = new RegExp(/\s+/g);  
                if (patt1.test(a)) {  
                    console.log("有空格");  
                   let d=a.replace(new RegExp(' ',"gm"), '')
                   console.log(a,'<----------------',d)
                   a=d;
                } 
               
                let repeateData = await storeInfoModel.findOne({ storeCode: bill.storeCode });
                if (repeateData != null) {
                    console.log('修改!!!!')
                    let b = await storeInfoModel.update({ storeCode: bill.storeCode }, {
                        sourceId: bill.storeCode,
                        storeCode: bill.storeCode,//门店编码
                        storeName: bill.storeName,//门店名称
                        store_classification: a,//门店类型
                        channel: bill.channel,//渠道
                        contact_name: bill.contactName,//联系人姓名
                        phone: bill.phone,//电话
                        consumerCode: bill.consumerCode,//客户编码
                        consumerName: bill.consumerName,//客户名称
                        city: bill.storeCity,//城市
                        cityType: bill.cityType,//城市级别
                        store_address: bill.storeAdress,//门店地址
                        iT_Mall_name: bill.iT_Mall_name,//IT Mall name
                        iT_Mall_address: bill.iT_Mall_address,//IT Mall address
                    }, { upsert: true });
                   // console.log(b, 'b_b_b_b_b_b_b_b_b_b_b_b_b_', );
                } else {
                    console.log('添加!!!!')
                    let a_ = await storeInfoModel.create({
                        sourceId: bill.storeCode,
                        storeCode: bill.storeCode,//门店编码
                        storeName: bill.storeName,//门店名称
                        store_classification: a,//门店类型
                        channel: bill.channel,//渠道
                        contact_name: bill.contactName,//联系人姓名
                        phone: bill.phone,//电话
                        consumerCode: bill.consumerCode,//客户编码
                        consumerName: bill.consumerName,//客户名称
                        city: bill.storeCity,//城市
                        cityType: bill.cityType,//城市级别
                        store_address: bill.storeAdress,//门店地址
                        iT_Mall_name: bill.iT_Mall_name,//IT Mall name
                        iT_Mall_address: bill.iT_Mall_address,//IT Mall address
                    });
                    // console.log(a_, 'a_a_a_a_a_a_a_a_a_a_a_a_');
                } 
            }
        } else {
            console.log('门店编码或门店名称为空!!!!!')
            errList[col] = {
                errCode: bill.storeCode,
                errName: bill.storeName,
                comment: 1
            }
        }
    }) 
    for (const key in errList) { //移植dataCache当中的属性到list当中
        if (errList.hasOwnProperty(key)) {
            const element = errList[key];
            list.push(element)
        }
    }
    console.log('导入门店数据结束!!!!!!!!!!!!!!!')
    if (list.length == 0) {
        ctx.body = {
            code: 0,
            data: []
        }
        ctx.status = 200
    } else {
        ctx.body = {
            code: 1,
            data: list
        }
    }
    await next();
});

async function getUserIdByCode(code: string, cache: any,access_token:string) {
    if (cache[code]) {
        return cache[code]
    } else {
        let result = await dTalkApi.getDingDingAdressList(code,access_token);
        if (!result || !result.list || result.list.length == 0) {
            console.log("工号", code, "未找到");
            return null;
        }
        cache[code] = result.list[0];
        return cache[code]
    }
}

//导入客户信息表
router.post('/testConsumerInfoExcel', multer.single('file'), async (ctx: IRouterContext, next) => {
    let cache: any = {}
    let rp: any = ctx.request;
    let excel = xlsx.parse(rp.req.file.buffer);
    let data: any = excel[0];
    let undefinedData: any[] = [];
    let errList: any = []//错误数据列表
    let list: any[] = []
    let access_token=await dTalkApiUtil.getAccessToken();
    //row:行  col:列
    for (let col = 0; col < data.data.length; col++) {
        const row = data.data[col];
        //判断row 行是否为脏数据
        if(row.length == 0)
            continue ;
        if (!errList[col]) {
            errList[col] = {
                errJobnumber: '',
                errCode: '',
                errName: '',
                comment: 1
            }
        }
        let bill: any = {
            consumerCode: row[0],//客户编号
            consumerName: row[1],//客户名称
            city: row[2],//城市
            cityType: row[3],//城市等级
            employeeName: row[4],//员工姓名
            jobnumber: row[5],//员工工号
            typeOne: row[6],//客户类型1
            typeTwo: row[7],//客户类型2
            typeThree: row[8],//客户类型3
            typeFour: row[9] || '',//客户类型4
            consumerAddress:row[10], //客户地址
            typeFive: row[11] || '',//客户类型5
            typeSix: row[12] || '',//客户类型6
            typeSeven: row[13] || '',//客户类型7
            typeEight: row[14] || '',//客户类型8
            typeNine: row[15] || '', //客户类型9
            typeTen: row[16] || '' //客户类型10
        };
        if(bill.consumerCode&&bill.consumerName){
            if (col != 0) {
                let result = await getUserIdByCode(bill.jobnumber, cache,access_token)//(bill.jobnumber);
                if (!result) {
                    continue;
                }
                let userid = result.staffId || '';
                //判断导入的数据有重复,有就覆盖
                let month = moment().format('MM');
                let repeateData = await consumerRecoderModel.findOne({ consumerCode: bill.consumerCode, comment: 1 });
                if (repeateData) {
                    let b=await consumerRecoderModel.update({ consumerCode: bill.consumerCode, comment: 1 }, {
                        jobnumber: bill.jobnumber,
                        userid: userid,
                        userName: bill.employeeName,
                        consumerName: bill.consumerName,
                        consumerCode: bill.consumerCode,
                        city: bill.city,//客户所在城市名称
                        cityType: bill.cityType,//城市等级
                        date: moment().format('YYYY-MM-DD'), //写拜访记录的日期与导入客户数据的日期
                        isChuchai: false,//是否出差
                        comment:1,
                        month: month,
                        year:moment().format('YYYY'),
                        typeOne: bill.typeOne,//客户类型1
                        typeTwo: bill.typeTwo,//客户类型2
                        typeThree: bill.typeThree,//客户类型3
                        typeFour: bill.typeFour, //客户类型4
                        typeFive: bill.typeFive,//客户类型5
                        typeSix: bill.typeSix,//客户类型6
                        typeSeven: bill.typeSeven,//客户类型7
                        typeEight: bill.typeEight,//客户类型8
                        typeNine: bill.typeNine, //客户类型9
                        typeTen: bill.typeTen, //客户类型10
                        consumerAddress:bill.consumerAddress //客户地址
                    }, { upsert: false });
                    console.log('修改>>>>',b,'b_b_b_b_b_b_b_',bill.consumerCode)
                } else {
                    console.log('执行添加!',col)
                    let b=await consumerRecoderModel.create( {
                        sourceId: userid + '&' + '',
                        jobnumber: bill.jobnumber,
                        userid: userid,//员工的userid
                        userName: bill.employeeName,
                        consumerName: bill.consumerName,
                        consumerCode: bill.consumerCode,
                        city: bill.city,//客户所在城市名称
                        cityType: bill.cityType,//城市等级
                        typeOne: bill.typeOne,//客户类型1====customerType<-----visitMonthPlan
                        typeTwo: bill.typeTwo,//客户类型2
                        typeThree: bill.typeThree,//客户类型3
                        typeFour: bill.typeFour, //客户类型4
                        typeFive: bill.typeFive,//客户类型5
                        typeSix: bill.typeSix,//客户类型6
                        typeSeven: bill.typeSeven,//客户类型7
                        typeEight: bill.typeEight,//客户类型8
                        typeNine: bill.typeNine, //客户类型9
                        typeTen: bill.typeTen, //客户类型10
                        consumerAddress:bill.consumerAddress, //客户地址
                        comment: 1,//说明  '导入的客户信息数据'与'拜访记录'
                        timestamp: '',
                        month: month,
                        date: moment().format('YYYY-MM-DD'), //写拜访记录的日期与导入客户数据的日期
                        year:moment().format('YYYY'),
                        planDate: '',//拜访日期
                        planType: '',//拜访类型
                        planPurpose: '',//拜访计划的拜访目的
                        consumerType: '',
                        vistitWay: '',
                        position: '',
                        isChuchai: false,//是否出差
                        picture: '',
                        purpose: '',  //记录的拜访目的          
                        talkingPerson: '',//洽谈姓名
                        job: '',//洽谈职位
                        connection: '',//联系方式
                        visitResult: '',
                        followMatter: '',
                        cooperation: '' //合作意向  
                    });
                    console.log('执行添加>>>>',b._id,'b_b_b_b_b_b_b_',b.consumerCode)
                }
                if(!result){
                    undefinedData.push(bill.jobnumber)
                }
            }
           
        }else{
            console.log('客户编码或客户名称为空!!!!!')
            errList[col]={
                errJobnumber:bill.jobnumber,
                errCode: bill.consumerCode,
                errName:bill.consumerName,
                comment:1
            }
        }
    }
    for (const key in errList) { //移植dataCache当中的属性到list当中
        if (errList.hasOwnProperty(key)) {
            const element = errList[key];
            list.push(element)
        }
    }
    console.log(list,'导入客户数据结束!!!!!!!!!!!')
    if(list.length==0){
        ctx.body = { 
            code: 0 ,
            data:[]
        }
        ctx.status = 200
    }else{
        ctx.body={
            code:1,
            data:list
        }
    }
    await next();
});

//导入员工月拜访数据计划
router.post('/leadeMonthPlanExcel', multer.single('file'), async (ctx: IRouterContext, next) => {
    let user = dingTalkApi.decode(ctx.header.authorization);
    let rp: any = ctx.request;
    let excel = xlsx.parse(rp.req.file.buffer);
    let excelObj = excel[0].data;
    let dateArr: any = [];
    let newDateArr:any[]=[];
    let json: any[] = [];
    let month: any = '';
    let removeList: any[] = []
    //  row:行  col:列
    //解析数据:   一行一行的解析数据
    excelObj.map(async (row: any, col: any) => {
        //获取拜访日期并去重
        if (col == 0) {//读取第一行时
            let json4: any = {};
            if(row.length>0){
                for (let i = 0; i < row.length; i++) {
                    if (!json4[row[i]]) {
                        dateArr.push(row[i]);
                        json4[row[i]] = 1;
                    }
                }
            }
        } else {
            //计划的具体数据
            let json2: any[] = []
            for (var i = 0; i < (dateArr.length - 1); i++) {
                let bill: any = {
                    employeeName: row[2 * 0],
                    planDate2: dateArr[i + 1],//拜访日期
                    code: row[2 * i + 1],//客户编码
                    consumerName: row[2 * i + 2],//客户名称
                };
                let date3 = new Date(1900, 0, bill.planDate2 - 1);
                let planDate2 = moment(date3).format('YYYY-MM-DD');
                if(!(newDateArr.includes(planDate2))){
                    newDateArr.push(planDate2);
                }
                let bill2: any = {
                    employeeName: row[2 * 0],
                    planDate2: planDate2,//拜访日期
                    code: row[2 * i + 1],//客户编码
                    consumerName: row[2 * i + 2],//客户名称
                }
                json2.push(bill2);
            }
            if (row[2 * 0] != undefined) {
                removeList[col - 1] = row[0];
            }
            json.push(json2);
        }
    });
    if (removeList.length != 0) {
        let rt = await visitMonthPlanModel.remove({ comment: 1, jobnumber: { $in: removeList },planDate:{$in:newDateArr},month: moment(dateArr[1]).format('MM') });
        console.log(rt, 're_re_re_re_re_re_re_re_re_re_')
        //保存数据---导入的月拜访计划
        await visitMonthPlanService.saveLeadeData(json); 
    }
    console.log('导入月计划数据结束!!!!!!!!!!!1')
    ctx.body = { code: 0 }
    ctx.status = 200
    await next();
})


//该接口是根据领导id获取其名下的员工id与员工name
router.post('/getuserInfoByLeaderId', async (ctx: IRouterContext, next: any) => {
    let rq: any = ctx.request;
    let res = rq.body;
    // console.log(res);
    let data = await syncDataService.getUserListByLeaderId(res.leaderId);
    if (data != null) {
        ctx.body = data;
        ctx.status = 200
    } else {
        ctx.body = [];
    }
    await next()
});

//(二)导出
//导出客户信息excel
router.post('/exportConsumerExcel', async (ctx: IRouterContext, next) => {
    let data = await consumerRecoderModel.find({ comment: 1 });
    let conf: any = [];
    conf.push(['客户编码', '客户名称', '城市', '城市等级', '员工名字', '员工工号', '员工id', '客户类型1', '客户类型2', '客户类型3', '客户类型4',]);
    //行
    let m_data: any = [];
    for (let index = 0; index < data.length; index++) {
        let a = data[index];
        conf.push([
            a.consumerCode,
            a.consumerName,
            a.city,
            a.cityType,
            a.userName,
            a.jobnumber,
            a.userid,
            a.typeOne,
            a.typeTwo,
            a.typeThree,
            a.typeFour
        ])
    };
    ctx.body = conf;
    ctx.status = 200
    await next();
})


//导出门店信息excel
router.post('/exportStoreExcel', async (ctx: IRouterContext, next) => {
    let data = await storeInfoModel.find({});
    let conf: any = [];//一行数据
    // console.log(data, 'data_data_data_data_data_data_data_data_')
    conf.push(['门店编码', '门店名称', '渠道', '城市等级', '门店等级', '联系人姓名', '联系人电话',
        '客户编号', '客户名称', '门店城市', '门店地址', '电脑城名称', '电脑城地址',]);
    for (let index = 0; index < data.length; index++) {
        let a = data[index];
        conf.push([
            a.storeCode,//门店编码
            a.storeName,//门店名称
            a.channel,//渠道
            a.cityType,//城市等级
            a.store_classification,//门店类型
            a.contact_name,//
            a.consumerCode,
            a.consumerName,
            a.phone,
            a.city,
            a.store_address,
            a.iT_Mall_name,
            a.iT_Mall_address
        ]);

    }
    // let buffer = xlsx.build([{name:`门店信息表`, data:conf }]);
    // fs.writeFileSync(`门店信息表.xlsx`,buffer)
    // console.log("----------门店信息导出excle-------------");
    ctx.body = conf;
    ctx.status = 200
    await next();
})

//报表 
//(三)导出客户拜访达成率Excel
router.post('/exportConsumerRateExcel', async (ctx: IRouterContext, next) => {
    let rp: any = ctx.request;
    let res = rp.body;
    console.log(res,'res_res_res_res_res_')
    let conf2: any[] = []
    let dateList: any = []
    let col: any[] = []//日期
    //参数: 
    let leaderId = res.leaderId;
    let character = res.character;//字段
    col.push('员工id');
    col.push('员工姓名')
    //获取日期
    let dateData=await formDataService.getDate( res.startDate,res.endDate)
    let date1 = parseInt(moment(dateData.startDate).format('DD'));
    let date2 = parseInt(moment(dateData.endDate).format('DD'));
    for (let index = 0; index <= (date2 - date1); index++) {
        let new_date = moment(dateData.startDate).add(index, 'days').format('YYYY-MM-DD');
        col.push(new_date + '(当日计划数量)', new_date + '(当日记录数量)', new_date+'(当日客户拜访达成率)')
        dateList.push(new_date);
    }
    col.push('本月客户拜访达成率')
    conf2.push(col) 
    //权限问题
    let access_token=await dTalkApiUtil.getAccessToken();
    let authority=await formDataService.getAuthrity(leaderId,access_token)
    let groupName=authority.groupName;
    let leaderDepts = authority.leaderDepts
    let arr=await emplModel.distinct('userId',{})
    if (groupName.includes('销售')||(!groupName.includes('销售')&&character)) {
        let userId: any = ''
        if (character == '' || character == null) {
            userId = leaderId
        } else {
            userId = character;
        }
        if(!(arr.includes(userId))){
            return;
        }
        let m_data: any = [];//一行数据
        m_data.push(userId)
        let data5=await emplModel.findOne({userId:userId,'$and':[{user:{$ne:null}},{user:{$ne:''}}]})
        //let data5=await visitMonthPlanModel.findOne({userId:userId,'$and':[{user:{$ne:null}},{user:{$ne:''}}]})
        if(data5){
            m_data.push(data5.userName);
        }else{
            let employeeInfo = await dTalkApi.getUserDetail(userId,access_token);
            if (employeeInfo&& employeeInfo.name) {
                 m_data.push(employeeInfo.name);//员工姓名
            }
        }
        let dateArr=await excelExportModel.distinct('date_',{userId:userId,"$and":[{date_:{$gte:dateData.startDate}},{date_:{$lte:dateData.endDate}}]})
        if (dateList.length > 0) {
            for (let index = 0; index < dateList.length; index++) {
                let date = dateList[index];
                if(dateArr.includes(date)){
                    let data_it=await excelExportModel.findOne({userId:userId,date_:date})
                    if(data_it){
                        m_data.push(data_it.visitCount_plan) //当日客户计划拜访数量
                        m_data.push(data_it.actuall_visitCount)   //当日客户实际拜访数量
                        m_data.push(data_it.consumerRate)//当日的客户拜访达成率
                    }
                }else{
                    m_data.push(0) //当日客户计划拜访数量
                    m_data.push(0)   //当日客户实际拜访数量
                    m_data.push('0.00')//当日的客户拜访达成率 
                }
            }
            //本月客户拜访达成率
            let consumerRate_one_employee3 = await formDataService.getConsumerRate_one_employeeDate1ToDate2(userId, dateData.startDate, dateData.endDate);
            m_data.push(consumerRate_one_employee3.toFixed(2))
        }
        conf2.push(m_data);
    } else {
        if (leaderDepts.length > 0) {
            let son_list2:any[]=[]
            for (let index = 0; index < leaderDepts.length; index++) {
                let dept = leaderDepts[index];
                let sonData = await dTalkApi.getSondepIdList(dept, true,access_token)
                let son_list = sonData.department;
                son_list.push(parseInt(dept));
                for (let key in son_list) {
                    if (son_list.hasOwnProperty(key)) {
                        let element = son_list[key];
                        if(!son_list2.includes(element)){//去重
                            son_list2.push(element)
                        }
                    }
                }
            }
            let employeeList=await visitMonthPlanModel.distinct("userId",{"$and":[{userId:{$in:arr}},{ userId: { $ne: '' } },{ userId: { $ne:null} },{deptId:{$in:son_list2}}]})
            if (!character) {
                let empArr=await excelExportModel.distinct('userId',{userId:{$in:employeeList},"$and":[{date_:{$gte:dateData.startDate}},{date_:{$lte:dateData.endDate}}]})
               //console.log(empArr,'=====excelExportModel===exportConsumerRateExcel===',dateData.startDate,dateData.endDate)
                if (employeeList.length > 0) {
                    for (let employeeindex = 0; employeeindex < employeeList.length; employeeindex++) {
                        let it = employeeList[employeeindex];
                        let m_data: any = []
                        m_data.push(it) //员工id
                        //console.log('1111111111111',it,'employeeindexemployeeindex',employeeindex)
                        let data5=await emplModel.findOne({userId:it,'$and':[{user:{$ne:null}},{user:{$ne:''}}]})
                        if(data5){
                            m_data.push(data5.userName);
                            //console.log('1111111111111',it,data5.userName)
                        }else{
                            let employeeInfo = await dTalkApi.getUserDetail(it,access_token);
                            if (employeeInfo&& employeeInfo.name) {
                                 m_data.push(employeeInfo.name);//员工姓名
                            }
                        }
                        if(empArr.includes(it)){ 
                            //console.log('22222222222',it,data5.userName)
                            let dateArr=await excelExportModel.distinct('date_',{userId:it,"$and":[{date_:{$gte:dateData.startDate}},{date_:{$lte:dateData.endDate}}]})
                            for (let index = 0; index < dateList.length; index++) {
                                let date = dateList[index];
                                if(dateArr.includes(date)){
                                    let data_it=await excelExportModel.findOne({userId:it,date_:date})
                                    if(data_it){
                                        m_data.push(data_it.visitCount_plan) //当日客户计划拜访数量
                                        m_data.push(data_it.actuall_visitCount)   //当日客户实际拜访数量
                                        m_data.push(data_it.consumerRate)//当日的客户拜访达成率
                                    }
                                }else{
                                    m_data.push(0) //当日客户计划拜访数量
                                    m_data.push(0)   //当日客户实际拜访数量
                                    m_data.push('0.00')//当日的客户拜访达成率 
                                }
                            }
                            //本月客户拜访达成率<=================新的
                            let data_9=await excelExportModel.find({userId:it,"$and":[{date_:{$gte:dateData.startDate}},{date_:{$lte:dateData.endDate}}]});
                            let numAll_plan=0;
                            let numAll_visit=0;
                            if(data_9.length>0){
                                data_9.map(it=>{
                                    if(it){
                                        numAll_plan+=it.visitCount_plan;
                                        numAll_visit+=it.actuall_visitCount;
                                    } 
                                })
                            }
                            let consumerRate_one_employee:any=0;
                            if(numAll_plan>0){
                                consumerRate_one_employee=(numAll_visit/numAll_plan);
                            }
                            m_data.push(consumerRate_one_employee.toFixed(2)) //本月客户拜访达成率
                            console.log(consumerRate_one_employee.toFixed(2),'======',employeeindex,'????',moment().format('HH:mm:ss'))
                        }else{
                          
                            for (let index = 0; index < dateList.length; index++) {
                                m_data.push(0) //当日客户计划拜访数量
                                m_data.push(0)   //当日客户实际拜访数量
                                m_data.push('0.00')//当日的客户拜访达成率   
                            }
                            m_data.push('0.00') //本月客户拜访达成率
                        }
                        conf2.push(m_data);
                        //console.log('====',consumerRate_one_employee.toFixed(2),'======',employeeindex,'===id==',it,'????',moment().format('HH:mm:ss'))
                    }
                }
            } 
        } else {
            console.log('对不起,你不是主管!无权限查看报表数据!!!!!!!!!!!!!');
            conf2.push([])
        }
    }
    // let buffer = xlsx.build([{name:`客户拜访达成率`, data:conf2 }]);
    // fs.writeFileSync(`客户拜访达成率.xlsx`,buffer)
    // console.log("----------客户拜访达成率导出excle-------------");
    ctx.body = conf2;
    ctx.status = 200
    await next();
})
 
//(一)导出签到率excel
router.post('/exportCheckInRateExcel', async (ctx: IRouterContext, next) => {
    let rp: any = ctx.request;
    let res = rp.body;
    console.log(res,'===?????',moment().format('HH:mm:ss'))
    let conf2: any[] = []
    let dateList: any = []
    let col: any[] = []//日期
    //参数:
    let leaderId = res.leaderId;
    let character = res.character;//字段
    col.push('员工id');
    col.push('员工姓名')
    col.push('月计划执行天数')
    //获取日期
    let dateData=await formDataService.getDate( res.startDate,res.endDate)
    let date1 = parseInt(moment(dateData.startDate).format('DD'));
    let date2 = parseInt(moment(dateData.endDate).format('DD'));
    for (let index = 0; index <= (date2 - date1); index++) {
        let new_date = moment(dateData.startDate).add(index, 'days').format('YYYY-MM-DD');
        col.push(new_date + '(当天的拜访计划的条数)', new_date + '(实际签到天数)','当天签到时间', new_date+'(签到率)')
        dateList.push(new_date);
    }
    conf2.push(col)
    //权限问题
    let access_token=await dTalkApiUtil.getAccessToken();
    let authority=await formDataService.getAuthrity(leaderId,access_token)
    let groupName=authority.groupName;
    let leaderDepts = authority.leaderDepts
    let arr=await emplModel.distinct('userId',{})
    if (groupName.includes('销售')||(!groupName.includes('销售')&&character)) {
        let userId: any = ''
        if (!character) {
            userId = leaderId
        } else {
            userId = character;
        }
        if(!(arr.includes(userId))){
            return;
        }
        let m_data: any = [];//一行数据
        m_data.push(userId)
        let data5=await emplModel.findOne({userId:userId,'$and':[{user:{$ne:null}},{user:{$ne:''}}]})
        if(data5){
            m_data.push(data5.userName);
        }else{
            let employeeInfo = await dTalkApi.getUserDetail(userId,access_token);
            if (employeeInfo&& employeeInfo.name) {
                 m_data.push(employeeInfo.name);//员工姓名
            }
        }
        let plan_day_count = await visitMonthPlanModel.distinct("planDate", { userId: userId, month: moment(dateData.endDate).format('MM'), "$or": [{ comment: 1, planStatus: { $ne: '同意' } }, { comment: 2, planStatus: '申请取消', cancelApply: '否' }, { comment: 2, planStatus: { $ne: '同意' }, cancelApply: '是' }, { planStatus: '写好拜访记录' }, { planStatus: '未写拜访记录' }] });
        m_data.push(plan_day_count.length)//月计划执行天数
        let dateArr=await excelExportModel.distinct('date_',{userId:userId,"$and":[{date_:{$gte:dateData.startDate}},{date_:{$lte:dateData.endDate}}]})
        if (dateList.length > 0) {
            for (let index = 0; index < dateList.length; index++) {
                let date = dateList[index];
                if(dateArr.includes(date)){
                    let data_it=await excelExportModel.findOne({userId:userId,date_:date})
                    if(data_it){
                        m_data.push(data_it.visitCount_plan)//当天的计划条数
                        m_data.push(data_it.actuall_checkIn)   //当日实际签到次数
                        m_data.push(data_it.checkInTime)//签到时间
                        m_data.push(data_it.checkInRate)//签到率
                    }
                }else{
                    m_data.push(0) 
                    m_data.push(0)  
                    m_data.push('')
                    m_data.push('0.00')//签到率
                }
            }
        }
        conf2.push(m_data);
    } else {
        if (leaderDepts.length > 0) {
            let son_list2:any[]=[]
            for (let index = 0; index < leaderDepts.length; index++) {
                let dept = leaderDepts[index];
                let sonData = await dTalkApi.getSondepIdList(dept, true,access_token)
                let son_list = sonData.department;
                son_list.push(parseInt(dept));
                for (let key in son_list) {
                    if (son_list.hasOwnProperty(key)) {
                        let element = son_list[key];
                        if(!son_list2.includes(element)){//去重
                            son_list2.push(element)
                        }
                    }
                }
            }
            let employeeList = await visitMonthPlanModel.distinct("userId", {userId:{$in:arr},"$and": [{ userId: { $ne:'' } }, { deptId: { $in: son_list2 } }] })
            //这个月那些员工有拜访计划
            let empArr2=await visitMonthPlanModel.distinct('userId',{userId:{$in:employeeList}, month: moment(dateData.endDate).format('MM'), "$or": [{ comment: 1, planStatus: { $ne: '同意' } }, { comment: 2, planStatus: '申请取消', cancelApply: '否' }, { comment: 2, planStatus: { $ne: '同意' }, cancelApply: '是' }, { planStatus: '写好拜访记录' }, { planStatus: '未写拜访记录' }] })
            if (!character ) {
                let empArr=await excelExportModel.distinct('userId',{userId:{$in:employeeList},"$and":[{date_:{$gte:dateData.startDate}},{date_:{$lte:dateData.endDate}}]})
                if (employeeList.length > 0) {
                    for (let employeeindex = 0; employeeindex < employeeList.length; employeeindex++) {
                        let it = employeeList[employeeindex];
                        let m_data: any = []
                        m_data.push(it) //员工id
                        let data5=await emplModel.findOne({userId:it,'$and':[{user:{$ne:null}},{user:{$ne:''}}]})
                        if(data5){
                            m_data.push(data5.userName);
                        }else{
                            let employeeInfo = await dTalkApi.getUserDetail(it,access_token);
                            if (employeeInfo&& employeeInfo.name) {
                                 m_data.push(employeeInfo.name);//员工姓名
                            }
                        }
                        //月计划执行天数
                        if(empArr2.includes(it)){
                            let plan_day_count = await visitMonthPlanModel.distinct("planDate", { userId: it, month: moment(dateData.endDate).format('MM'), "$or": [{ comment: 1, planStatus: { $ne: '同意' } }, { comment: 2, planStatus: '申请取消', cancelApply: '否' }, { comment: 2, planStatus: { $ne: '同意' }, cancelApply: '是' }, { planStatus: '写好拜访记录' }, { planStatus: '未写拜访记录' }] });
                            m_data.push(plan_day_count.length)
                        }else{
                            m_data.push(0)
                        }
                        if(empArr.includes(it)){
                            let dateArr=await excelExportModel.distinct('date_',{userId:it,"$and":[{date_:{$gte:dateData.startDate}},{date_:{$lte:dateData.endDate}}]})
                            if (dateList.length > 0) {
                                for (let index = 0; index < dateList.length; index++) {
                                    let date = dateList[index];
                                    if(dateArr.includes(date)){
                                        let data_it=await excelExportModel.findOne({userId:it,date_:date})
                                        if(data_it){
                                            m_data.push(data_it.visitCount_plan) //当天的拜访计划的条数
                                            m_data.push(data_it.actuall_checkIn)  //实际签到天数
                                            m_data.push(data_it.checkInTime)//当天的签到时间
                                            m_data.push(data_it.checkInRate)//当天的签到率
                                        }
                                    }else{
                                        m_data.push(0) 
                                        m_data.push(0)  
                                        m_data.push('')
                                        m_data.push('0.00')//签到率
                                    }
                                }
                            }
                        }else{
                            for (let index = 0; index < dateList.length; index++) {
                                m_data.push(0) 
                                m_data.push(0)  
                                m_data.push('')
                                m_data.push('0.00')//签到率
                            } 
                        }
                        conf2.push(m_data);
                        console.log(employeeindex,'===',it,'?????',moment().format('HH:mm:ss'))
                    }
                } 
            } 
        } else {
            console.log('对不起,你不是主管!无权限查看报表数据!!!!!!!!!!!!!');
            conf2.push([])
        }
    }
    // let buffer = xlsx.build([{name:`签到率`, data:conf2 }]);
    // fs.writeFileSync(`签到率.xlsx`,buffer)
    // console.log("----------签到率导出excle-------------");
    ctx.body = conf2;
    ctx.status = 200
    await next();
}) 

//(二)导出出差频率Excel
router.post('/exportTravelFrequencyExcel', async (ctx: IRouterContext, next) => {
    let rp: any = ctx.request;
    let res = rp.body;
    console.log(res,'=1111111111==????',moment().format('HH:mm:ss'))
    let conf2: any[] = []
    let dateList: any = []
    let col: any[] = []//日期
    //参数:
    let leaderId = res.leaderId;
    let character = res.character;//字段
    col.push('员工id');
    col.push('员工姓名')
    col.push('月计划执行天数')
    //获取日期
    let dateData=await formDataService.getDate( res.startDate,res.endDate)
    let date1 = parseInt(moment(dateData.startDate).format('DD'));
    let date2 = parseInt(moment(dateData.endDate).format('DD'));
    for (let index = 0; index <= (date2 - date1); index++) {
        let new_date = moment(dateData.startDate).add(index, 'days').format('YYYY-MM-DD');
        col.push(new_date + '(当天计划条数)',new_date + '(当天是否在非工作地签到)')
        dateList.push(new_date);
    }
    col.push('本月合计出差天数')
    conf2.push(col)
    //权限问题
    let access_token=await dTalkApiUtil.getAccessToken();
    let authority=await formDataService.getAuthrity(leaderId,access_token)
    let groupName=authority.groupName;
    let leaderDepts = authority.leaderDepts
    let arr=await emplModel.distinct('userId',{})
    if (groupName.includes('销售')||(!groupName.includes('销售')&&character)) {
        let userId: any = ''
        if (character == '' || character == null) {
            userId = leaderId
        } else {
            userId = character;
        }
        if(!(arr.includes(userId))){
            return;
        }
        let m_data: any = [];//一行数据
        m_data.push(userId)
        let data5=await emplModel.findOne({userId:userId,'$and':[{user:{$ne:null}},{user:{$ne:''}}]})
        if(data5){
            m_data.push(data5.userName);
        }else{
            let employeeInfo = await dTalkApi.getUserDetail(userId,access_token);
            if (employeeInfo&& employeeInfo.name) {
                 m_data.push(employeeInfo.name);//员工姓名
            }
        }
        //月计划执行天数
        let plan_day_count = await visitMonthPlanModel.distinct("planDate", { userId: userId, month: moment(dateData.endDate).format('MM'), "$or": [{ comment: 1, planStatus: { $ne: '同意' } }, { comment: 2, planStatus: '申请取消', cancelApply: '否' }, { comment: 2, planStatus: { $ne: '同意' }, cancelApply: '是' }, { planStatus: '写好拜访记录' }, { planStatus: '未写拜访记录' }] });
        m_data.push(plan_day_count.length)
        if (dateList.length > 0) {
            for (let index = 0; index < dateList.length; index++) {
                let date = dateList[index];
                let data_it=await excelExportModel.findOne({userId:userId,date_:date})
                if(data_it){
                    m_data.push(data_it.visitCount_plan) //当天计划的条数
                    m_data.push(data_it.unBase_check)  //非BASE地签到天数
                }else{
                    m_data.push(0) 
                    m_data.push(0)  
                }
            }
        }
        //本月合计出差天数
        if(plan_day_count.length>0){
            let sto_date=await storeRecoderModel.distinct('planDate',{userid: userId,  isChuchai: true, "$and": [{ planDate: { "$gte": dateData.startDate } }, { planDate: { "$lte": dateData.endDate } }]})
            let con_date=await consumerRecoderModel.distinct('planDate',{userid: userId,  isChuchai: true,comment:2, "$and": [{ planDate: { "$gte":dateData.startDate } }, { planDate: { "$lte": dateData.endDate } }]})
            let arry_= con_date.concat(sto_date);
            if(arry_.length>0){
                let arr_=await formDataService.newArray(arry_)
                m_data.push(arr_.length)
            }else{
                 m_data.push(0) 
            }
        }else{
            m_data.push(0) 
        }
        conf2.push(m_data);
    } else {
        if (leaderDepts.length > 0) {
            let son_list2:any[]=[]
            for (let index = 0; index < leaderDepts.length; index++) {
                let dept = leaderDepts[index];
                let sonData = await dTalkApi.getSondepIdList(dept, true,access_token)
                let son_list = sonData.department;
                son_list.push(parseInt(dept));
                for (let key in son_list) {
                    if (son_list.hasOwnProperty(key)) {
                        let element = son_list[key];
                        if(!son_list2.includes(element)){//去重
                            son_list2.push(element)
                        }
                    }
                }
            }
            let employeeList = await visitMonthPlanModel.distinct("userId", {userId:{$in:arr},"$and": [{ userId: { $ne:'' } }, { deptId: { $in: son_list2 } }] })
            //这段时间哪些员工有过出差
            let sto_emp=await storeRecoderModel.distinct('userid',{userid:{$in:employeeList} ,  isChuchai: true, "$and": [{ planDate: { "$gte": dateData.startDate } }, { planDate: { "$lte": dateData.endDate } }]})
            let con_emp=await consumerRecoderModel.distinct('userid',{userid: {$in:employeeList},  isChuchai: true,comment:2, "$and": [{ planDate: { "$gte":dateData.startDate } }, { planDate: { "$lte": dateData.endDate } }]})
            let empArr=sto_emp.concat(con_emp)//出过差的人员列表
            //哪些员工这段时间有月计划
            let empArr2=await visitMonthPlanModel.distinct('userId',{ userId:{$in:employeeList}, month: moment(dateData.endDate).format('MM'), "$or": [{ comment: 1, planStatus: { $ne: '同意' } }, { comment: 2, planStatus: '申请取消', cancelApply: '否' }, { comment: 2, planStatus: { $ne: '同意' }, cancelApply: '是' }, { planStatus: '写好拜访记录' }, { planStatus: '未写拜访记录' }] })
            if (!character) {
                let empArr3=await excelExportModel.distinct('userId',{userId:{$in:employeeList},"$and":[{date_:{$gte:dateData.startDate}},{date_:{$lte:dateData.endDate}}]})
                if (employeeList.length > 0) {
                    for (let employeeindex = 0; employeeindex < employeeList.length; employeeindex++) {
                        let it = employeeList[employeeindex];
                        let m_data: any = []
                        m_data.push(it) //员工id
                        let data5=await emplModel.findOne({userId:it,'$and':[{user:{$ne:null}},{user:{$ne:''}}]})
                        if(data5){
                            m_data.push(data5.userName);
                        }else{
                            let employeeInfo = await dTalkApi.getUserDetail(it,access_token);
                            if (employeeInfo&& employeeInfo.name) {
                                 m_data.push(employeeInfo.name);//员工姓名
                            }
                        }
                        //月计划执行天数
                        if(empArr2.includes(it)){
                            let plan_day_count = await visitMonthPlanModel.distinct("planDate", { userId: it, month: moment(dateData.endDate).format('MM'), "$or": [{ comment: 1, planStatus: { $ne: '同意' } }, { comment: 2, planStatus: '申请取消', cancelApply: '否' }, { comment: 2, planStatus: { $ne: '同意' }, cancelApply: '是' }, { planStatus: '写好拜访记录' }, { planStatus: '未写拜访记录' }] });
                            m_data.push(plan_day_count.length)
                        }else{
                            m_data.push(0)
                        }
                        if(empArr3.includes(it)){
                            let dateArr=await excelExportModel.distinct('date_',{userId:it,"$and":[{date_:{$gte:dateData.startDate}},{date_:{$lte:dateData.endDate}}]}) 
                            if (dateList.length > 0) {
                                for (let index = 0; index < dateList.length; index++) {
                                    let date = dateList[index];
                                    if(dateArr.includes(date)){
                                        let data_it=await excelExportModel.findOne({userId:it,date_:date})
                                        if(data_it){
                                            m_data.push(data_it.visitCount_plan) //当天计划的条数
                                            m_data.push(data_it.unBase_check)  //当天是否在非工作地签到
                                        }
                                    }else{
                                        m_data.push(0) 
                                        m_data.push(0)  
                                    }
                                }
                            }
                        }else{
                            for (let index = 0; index < dateList.length; index++) {
                                m_data.push(0) 
                                m_data.push(0)  
                            }
                        }
                        //本月合计出差天数
                        if(empArr.includes(it)){
                            let sto_date=await storeRecoderModel.distinct('planDate',{userid: it,  isChuchai: true, "$and": [{ planDate: { "$gte": dateData.startDate } }, { planDate: { "$lte": dateData.endDate } }]})
                            let con_date=await consumerRecoderModel.distinct('planDate',{userid: it,  isChuchai: true,comment:2, "$and": [{ planDate: { "$gte":dateData.startDate } }, { planDate: { "$lte": dateData.endDate } }]})
                            let arry_= con_date.concat(sto_date);
                            if(arry_.length>0){
                                let arr_=await formDataService.newArray(arry_)
                                m_data.push(arr_.length)
                            }else{
                                 m_data.push(0) 
                            }
                        }else{
                            m_data.push(0) 
                        }
                        conf2.push(m_data)
                        console.log(employeeindex,'=1111111111==',it,'????',moment().format('HH:mm:ss'))
                    }
                }
            } 
        } else {
            console.log('对不起,你不是主管!无权限查看报表数据!!!!!!!!!!!!!');
            conf2.push([])
        }
    }
    // let buffer = xlsx.build([{name:`出差频率`, data:conf2 }]);
    // fs.writeFileSync(`出差频率.xlsx`,buffer)
    // console.log("----------出差频率导出excle-------------");
    ctx.body = conf2;
    ctx.status = 200
    await next();
}) 

//(四)导出新客户拜访达成率Excel
router.post('/exportNewConsumerRateExcel', async (ctx: IRouterContext, next) => {
    let rp: any = ctx.request;
    let res = rp.body;
    console.log(res,'=1111111111==????',moment().format('HH:mm:ss'))
    let conf2: any[] = []
    let dateList: any = []
    let col: any[] = []//日期
    let arr=await emplModel.distinct('userId',{})
    //参数:
    let leaderId = res.leaderId;
    let character = res.character;//字段
    col.push('员工id');
    col.push('员工姓名')
    //获取日期
    let dateData=await formDataService.getDate( res.startDate,res.endDate)
    let date1 = parseInt(moment(dateData.startDate).format('DD'));
    let date2 = parseInt(moment(dateData.endDate).format('DD'));
    for (let index = 0; index <= (date2 - date1); index++) {
        let new_date = moment(dateData.startDate).add(index, 'days').format('YYYY-MM-DD');
        col.push(new_date + '(当日新客户计划数量)', new_date + '(当日新客户记录数量)', new_date+'(当日新客户拜访达成率)')
        dateList.push(new_date);
    }
    col.push('本月新客户拜访达成率')
    conf2.push(col)
    //权限问题
    let access_token=await dTalkApiUtil.getAccessToken();
    let authority=await formDataService.getAuthrity(leaderId,access_token)
    let groupName=authority.groupName;
    if (groupName.includes('销售')||(!groupName.includes('销售')&&character)) {
        let userId: any = ''
        if (!character) {
            userId = leaderId
        } else {
            userId = character;
        }
        if(!(arr.includes(userId))){
            return;
        }
        let m_data: any = [];//一行数据
        m_data.push(userId)
        let data5=await emplModel.findOne({userId:userId,'$and':[{user:{$ne:null}},{user:{$ne:''}}]})
        if(data5){
            m_data.push(data5.userName);
        }else{
            let employeeInfo = await dTalkApi.getUserDetail(userId,access_token);
            if (employeeInfo&& employeeInfo.name) {
                 m_data.push(employeeInfo.name);//员工姓名
            }
        }
        let dateArr=await excelExportModel.distinct('date_',{userId:userId,"$and":[{date_:{$gte:dateData.startDate}},{date_:{$lte:dateData.endDate}}]})
        if (dateList.length > 0) {
            for (let index = 0; index < dateList.length; index++) {
                let date = dateList[index];
                if(dateArr.includes(date)){
                    let data_it=await excelExportModel.findOne({userId:userId,date_:date})
                    if(data_it){
                        m_data.push(data_it.newConsumerCount_plan) //计划拜访的新客户数量
                        m_data.push(data_it.newConsumerCount_visit)  //实际已经拜访的新客户数量
                        m_data.push(data_it.newConsumerRate)//新客户拜访达成率
                    }
                }else{
                    m_data.push(0) 
                    m_data.push(0)  
                    m_data.push('0.00')
                }
               
            }
            //本月新客户拜访达成率
            let consumerRate_one_employee3 = await formDataService.getRate_NewConsumer_one_employeeDate1ToDate2(userId, dateData.startDate, dateData.endDate);
            m_data.push(consumerRate_one_employee3.toFixed(2))
        }
        conf2.push(m_data);
    } else {
        if (authority.leaderDepts.length > 0) {
            let son_list2:any[]=[]
            for (let index = 0; index < authority.leaderDepts.length; index++) {
                let dept = authority.leaderDepts[index];
                let sonData = await dTalkApi.getSondepIdList(dept, true,access_token)
                let son_list = sonData.department;
                son_list.push(parseInt(dept));
                for (let key in son_list) {
                    if (son_list.hasOwnProperty(key)) {
                        let element = son_list[key];
                        if(!son_list2.includes(element)){//去重
                            son_list2.push(element)
                        }
                    }
                }
            }
            let employeeList = await visitMonthPlanModel.distinct("userId", {userId:{$in:arr},"$and": [{ userId: { $ne:'' } }, { deptId: { $in: son_list2 } }] })
            //这段时间哪些员工有新客户拜访计划
            let empArr=await visitMonthPlanModel.distinct('userId',{userId:{$in:employeeList},plantype:'新拓展客户', month: moment(dateData.endDate).format('MM'), "$or": [{ comment: 1, planStatus: { $ne: '同意' } }, { comment: 2, planStatus: '申请取消', cancelApply: '否' }, { comment: 2, planStatus: { $ne: '同意' }, cancelApply: '是' }, { planStatus: '写好拜访记录' }, { planStatus: '未写拜访记录' }]})
            if (!character) {
                if (employeeList.length > 0) {
                    for (let userindex = 0; userindex < employeeList.length; userindex++) {
                        let it = employeeList[userindex];
                        let m_data: any = []
                        m_data.push(it) //员工id
                        let data5=await emplModel.findOne({userId:it,'$and':[{user:{$ne:null}},{user:{$ne:''}}]})
                        if(data5){
                            m_data.push(data5.userName);
                        }else{
                            let employeeInfo = await dTalkApi.getUserDetail(it,access_token);
                            if (employeeInfo&& employeeInfo.name) {
                                m_data.push(employeeInfo.name);//员工姓名
                           }
                        }
                        if (dateList.length > 0) {
                            let dateArr=await excelExportModel.distinct('date_',{userId:it,"$and":[{date_:{$gte:dateData.startDate}},{date_:{$lte:dateData.endDate}}]})
                            for (let index = 0; index < dateList.length; index++) {
                                let date = dateList[index];
                                if(dateArr.includes(date)){
                                    let data_it=await excelExportModel.findOne({userId:it,date_:date})
                                    if(data_it){
                                        m_data.push(data_it.newConsumerCount_plan) //计划拜访的新客户数量
                                        m_data.push(data_it.newConsumerCount_visit)  //实际已经拜访的新客户数量
                                        m_data.push(data_it.newConsumerRate)//新客户拜访达成率
                                    }
                                }else{
                                    m_data.push(0) 
                                    m_data.push(0)  
                                    m_data.push('0.00')
                                }
                            }
                        }
                        //本月新客户拜访达成率
                        if(empArr.includes(it)){
                            //本月新客户拜访达成率<=================新的
                            let data_9=await excelExportModel.find({userId:it,"$and":[{date_:{$gte:dateData.startDate}},{date_:{$lte:dateData.endDate}}]});
                            let numAll_plan=0;
                            let numAll_visit=0;
                            if(data_9.length>0){
                                data_9.map(it=>{
                                    if(it){
                                        numAll_plan+=it.newConsumerCount_plan;
                                        numAll_visit+=it.newConsumerCount_visit;
                                    } 
                                })
                            }
                            let consumerRate_one_employee:any=0;
                            if(numAll_plan>0){
                                consumerRate_one_employee=(numAll_visit/numAll_plan);
                            }
                            m_data.push(consumerRate_one_employee.toFixed(2))
                            //let consumerRate_one_employee3 = await formDataService.getRate_NewConsumer_one_employeeDate1ToDate2(it, dateData.startDate, dateData.endDate);
                        }else{
                            m_data.push('0.00')
                        }
                        conf2.push(m_data);
                        console.log(userindex,'===',it,'?????',moment().format('HH:mm:ss'))
                    };
                } 
            }
        } else {
            console.log('对不起,你不是主管!无权限查看报表数据!!!!!!!!!!!!!');
            conf2.push([])
        }
    }
    // let buffer = xlsx.build([{name:`新客户拜访达成率`, data:conf2 }]);
    // fs.writeFileSync(`新客户拜访达成率.xlsx`,buffer)
    // console.log("----------新客户拜访达成率导出excle-------------");

    ctx.body = conf2;
    ctx.status = 200;
    await next()
}) 

//(五)导出已拜访门店拜访达标率Excel
router.post('/exportReachStoreStandardRateExcel', async (ctx: IRouterContext, next) => {
    let rp: any = ctx.request;
    let res = rp.body;
    console.log(res,'=1111111111==????',moment().format('HH:mm:ss'))
    let conf2: any[] = []
    let dateList: any = []
    let col: any[] = []//日期
    let arr=await emplModel.distinct('userId',{})
    //参数:
    let leaderId = res.leaderId;
    let character = res.character;//字段
    col.push('员工id');
    col.push('员工姓名')
    //获取日期
    let dateData=await formDataService.getDate( res.startDate,res.endDate)
    let date1 = parseInt(moment(dateData.startDate).format('DD'));
    let date2 = parseInt(moment(dateData.endDate).format('DD'));
    for (let index = 0; index <= (date2 - date1); index++) {
        let new_date = moment(dateData.startDate).add(index, 'days').format('YYYY-MM-DD');
        col.push(new_date + '(当日已拜访门店数量)', new_date + '(当日拜访门店中门店达标的数量)', new_date+'(当日已拜访门店拜访达标率)')
        dateList.push(new_date);
    }
    col.push('本月已拜访门店拜访达标率')
    conf2.push(col)
    //权限问题
    let access_token=await dTalkApiUtil.getAccessToken();
    let authority=await formDataService.getAuthrity(leaderId,access_token)
    let groupName=authority.groupName;
    if (groupName.includes('销售')||(!groupName.includes('销售')&&character)){
        let userId: any = ''
        if (!character) {
            userId = leaderId
        } else {
            userId = character;
        }
        if(!(arr.includes(userId))){
            return;
        }
        let m_data: any = [];//一行数据
        m_data.push(userId)
        let data5=await emplModel.findOne({userId:userId,'$and':[{user:{$ne:null}},{user:{$ne:''}}]})
        if(data5){
            m_data.push(data5.userName);
        }else{
            let employeeInfo = await dTalkApi.getUserDetail(userId,access_token);
            if (employeeInfo&& employeeInfo.name) {
                 m_data.push(employeeInfo.name);//员工姓名
            }
        }
        let dateArr=await excelExportModel.distinct('date_',{userId:userId,"$and":[{date_:{$gte:dateData.startDate}},{date_:{$lte:dateData.endDate}}]})
        if (dateList.length > 0) {
            for (let index = 0; index < dateList.length; index++) {
                let date = dateList[index];
                if(dateArr.includes(date)){
                    let data_it=await excelExportModel.findOne({userId:userId,date_:date})
                    if(data_it){
                        m_data.push(data_it.visitedStoreCount)   //门店已经拜访的数量
                        m_data.push(data_it.reachStandardStoreCount)  //门店已经拜访的数量中店铺达标的数量
                        m_data.push(data_it.reachStandardRate)//已拜访门店拜访达标率
                    }
                }else{
                    m_data.push(0) 
                    m_data.push(0)  
                    m_data.push('0.00')
                }
            }
        }
        // 本月已拜访门店拜访达标率
        let reachStoreRate_one_employee2 = await formDataService.getReachStoreRate_one_employeeDate1ToDate2(userId, dateData.startDate, dateData.endDate);
        m_data.push(reachStoreRate_one_employee2.toFixed(2))
        conf2.push(m_data);
    } else {
        if (authority.leaderDepts.length > 0) {
            let son_list2:any[]=[]
            for (let index = 0; index < authority.leaderDepts.length; index++) {
                let dept = authority.leaderDepts[index];
                let sonData = await dTalkApi.getSondepIdList(dept, true,access_token)
                let son_list = sonData.department;
                son_list.push(parseInt(dept));
                for (let key in son_list) {
                    if (son_list.hasOwnProperty(key)) {
                        let element = son_list[key];
                        if(!son_list2.includes(element)){//去重
                            son_list2.push(element)
                        }
                    }
                }
            }
            let employeeList = await visitMonthPlanModel.distinct("userId", {userId:{$in:arr},"$and": [{ userId: { $ne:'' } }, { deptId: { $in: son_list2 } }] })
            //本月哪些员工有门店拜访记录
            let empArr=await storeRecoderModel.distinct('userid',{userid:{$in:employeeList},month:moment(dateData.endDate).format('MM')})
            //本月哪些员工的门店拜访中是达标的
            let empArr2=await storeRecoderModel.distinct('userid',{userid:{$in:employeeList},isListStandard:true,month:moment(dateData.endDate).format('MM')})
            if (!character) {
                if (employeeList.length > 0) {
                    for (let employeeindex = 0; employeeindex < employeeList.length; employeeindex++) {
                        let it = employeeList[employeeindex];
                        let m_data: any = []
                        m_data.push(it) //员工id
                        let data5=await emplModel.findOne({userId:it,'$and':[{user:{$ne:null}},{user:{$ne:''}}]})
                        if(data5){
                            m_data.push(data5.userName);
                        }else{
                            let employeeInfo = await dTalkApi.getUserDetail(it,access_token);
                            if (employeeInfo != '' && employeeInfo != null) {
                                 m_data.push(employeeInfo.name);//员工姓名
                            }
                        }
                        if(empArr.includes(it)){
                            let dateArr=await excelExportModel.distinct('date_',{userId:it,"$and":[{date_:{$gte:dateData.startDate}},{date_:{$lte:dateData.endDate}}]})
                            if (dateList.length > 0) {
                                for (let index = 0; index < dateList.length; index++) {
                                    let date = dateList[index];
                                    if(dateArr.includes(date)){
                                        let data_it=await excelExportModel.findOne({userId:it,date_:date})
                                        if(data_it){
                                            m_data.push(data_it.visitedStoreCount)   //门店已经拜访的数量
                                            m_data.push(data_it.reachStandardStoreCount)  //门店已经拜访的数量中店铺达标的数量
                                            m_data.push(data_it.reachStandardRate)//已拜访门店拜访达标率
                                        }
                                    }else{
                                        m_data.push(0) 
                                        m_data.push(0)  
                                        m_data.push('0.00')
                                    }
                                }
                            }
                        }else{
                            //没有门店拜访记录的
                            for (let index = 0; index < dateList.length; index++) {
                                m_data.push(0) 
                                m_data.push(0)  
                                m_data.push('0.00')
                            }
                        }
                        // 本月已拜访门店拜访达标率
                        if(empArr2.includes(it)){
                            let reachStoreRate_one_employee2 = await formDataService.getReachStoreRate_one_employeeDate1ToDate2(it, dateData.startDate, dateData.endDate);
                            m_data.push(reachStoreRate_one_employee2.toFixed(2))
                        }else{
                            m_data.push('0.00')
                        }
                        conf2.push(m_data);
                        console.log(employeeindex,'==1111=',it,'??????????',moment().format('HH:mm:ss'))
                    }
                } 
            } 
        }else{
            console.log('对不起,你不是主管!无权限查看报表数据!!!!!!!!!!!!!');
            conf2.push([])
        }
    }
    // let buffer = xlsx.build([{name:`已拜访门店拜访达标率`, data:conf2 }]);
    // fs.writeFileSync(`已拜访门店拜访达标率.xlsx`,buffer)
    // console.log("----------已拜访门店拜访达标率导出excle-------------");
    ctx.body = conf2;
    ctx.status = 200
    await next();
});

//(六)导出门店拜访进度Excel
router.post('/exportVisitStoreSchaduleExcel', async (ctx: IRouterContext, next) => {
    let rp: any = ctx.request;
    let res = rp.body;
    console.log(res,'res_res_res_res_res_res_',moment().format('HH:mm:ss'))
    let conf2: any[] = []
    let dateList: any = []
    let col: any[] = []//日期
    let arr=await emplModel.distinct('userId',{})
    //参数:
    let leaderId = res.leaderId;
    let character = res.character;//字段
    col.push('员工id');
    col.push('员工姓名')
    //获取日期
    let dateData=await formDataService.getDate( res.startDate,res.endDate)
    let date1 = parseInt(moment(dateData.startDate).format('DD'));
    let date2 = parseInt(moment(dateData.endDate).format('DD'));
    for (let index = 0; index <= (date2 - date1); index++) {
        let new_date = moment(dateData.startDate).add(index, 'days').format('YYYY-MM-DD');
        col.push(new_date + '(员工名下门店总数量)', new_date + '(拜访门店的记录数量)', new_date+'(当天的门店拜访进度)')
        dateList.push(new_date);
    }
    col.push('本月的拜访门店进度')
    conf2.push(col)
    //权限问题
    let access_token=await dTalkApiUtil.getAccessToken();
    let authority=await formDataService.getAuthrity(leaderId,access_token)
    let groupName=authority.groupName;
    //哪些员工有门店
    let codeArr=await storeInfoModel.distinct('consumerCode',{});
    let empArr2=await consumerRecoderModel.distinct('userid',{comment:1,consumerCode:{$in:codeArr}});
    if (groupName.includes('销售')||(!groupName.includes('销售')&&character)) {
        let userId: any = ''
        if (!character) {
            userId = leaderId
        } else {
            userId = character;
        }
        if(!(arr.includes(userId))){
            return;
        }
        let m_data: any = [];//一行数据
        m_data.push(userId)
        let data5=await emplModel.findOne({userId:userId,'$and':[{user:{$ne:null}},{user:{$ne:''}}]})
        if(data5){
            m_data.push(data5.userName);
        }else{
            let employeeInfo = await dTalkApi.getUserDetail(userId,access_token);
            if (employeeInfo&& employeeInfo.name) {
                 m_data.push(employeeInfo.name);//员工姓名
            }
        }
        let dateArr=await excelExportModel.distinct('date_',{userId:userId,"$and":[{date_:{$gte:dateData.startDate}},{date_:{$lte:dateData.endDate}}]})
        if (dateList.length > 0) {
            for (let index = 0; index < dateList.length; index++) {
                let date = dateList[index];
                if(dateArr.includes(date)){
                    let data_it=await excelExportModel.findOne({userId:userId,date_:date})
                    if(data_it){
                        m_data.push(data_it.totalCountStore)  //员工名下所有门店数量
                        m_data.push(data_it.visitedStoreCount)   //员工已经拜访的门店数量
                        m_data.push(data_it.storeSchadule)//门店拜访进度
                    }
                }else{
                    m_data.push(0) 
                    m_data.push(0)  
                    m_data.push('0.00')
                }
               
            }
        }
        // 本月门店拜访进度
        if(empArr2.includes(userId)){
            let storeSchedule_one_employee2 = await formDataService.getShop_schedule_one_employeeDate1ToDate2(userId, dateData.startDate, dateData.endDate);
            m_data.push(storeSchedule_one_employee2.toFixed(2))
        }else{
            m_data.push('0.00')
        }
        conf2.push(m_data);
    } else {
        if (authority.leaderDepts.length > 0) {
            let son_list2:any[]=[]
            for (let index = 0; index < authority.leaderDepts.length; index++) {
                let dept = authority.leaderDepts[index];
                let sonData = await dTalkApi.getSondepIdList(dept, true,access_token)
                let son_list = sonData.department;
                son_list.push(parseInt(dept));
                for (let key in son_list) {
                    if (son_list.hasOwnProperty(key)) {
                        let element = son_list[key];
                        if(!son_list2.includes(element)){//去重
                            son_list2.push(element)
                        }
                    }
                }
            }
            let employeeList = await visitMonthPlanModel.distinct("userId", {userId:{$in:arr},"$and": [{ userId: { $ne:'' } }, { deptId: { $in: son_list2 } }] })
            //这一段时间哪些员工有门店拜访记录
            let empArr=await storeRecoderModel.distinct('userid',{userid:{$in:employeeList},month:moment(dateData.endDate).format('MM')})
            if (!character) {
                if (employeeList.length > 0) {
                    for (let employeeindex = 0; employeeindex < employeeList.length; employeeindex++) {
                        let it = employeeList[employeeindex];
                        let m_data: any = []
                        m_data.push(it) //员工id
                        let name_:any=''
                        let data5=await emplModel.findOne({userId:it,'$and':[{user:{$ne:null}},{user:{$ne:''}}]})
                        if(data5){
                            name_=data5.userName
                        }else{
                            let employeeInfo = await dTalkApi.getUserDetail(it,access_token);
                            if (employeeInfo != '' && employeeInfo != null) {
                                 name_=employeeInfo.name
                            }
                        }
                        m_data.push(name_);
                        //门店拜访记录的员工
                        if(empArr.includes(it)){
                            let dateArr=await excelExportModel.distinct('date_',{userId:it,"$and":[{date_:{$gte:dateData.startDate}},{date_:{$lte:dateData.endDate}}]})
                            if (dateList.length > 0) {
                                for (let index = 0; index < dateList.length; index++) {
                                    let date = dateList[index];
                                    if(dateArr.includes(date)){
                                        let data_it=await excelExportModel.findOne({userId:it,date_:date})
                                        if(data_it){
                                            m_data.push(data_it.totalCountStore)  //员工名下所有门店数量
                                            m_data.push(data_it.visitedStoreCount)   //员工已经拜访的门店数量
                                            m_data.push(data_it.storeSchadule)//门店拜访进度
                                        }
                                    }else{
                                        m_data.push(0) 
                                        m_data.push(0)  
                                        m_data.push('0.00')
                                    }
                                }
                            }
                        }else{
                            for (let index = 0; index < dateList.length; index++) {
                                m_data.push(0) 
                                m_data.push(0)  
                                m_data.push('0.00')
                            } 
                        }
                        // 本月门店拜访进度
                        if(empArr2.includes(it)){
                            let storeSchedule_one_employee2 = await formDataService.getShop_schedule_one_employeeDate1ToDate2(it, dateData.startDate, dateData.endDate);
                            m_data.push(storeSchedule_one_employee2.toFixed(2))
                        }else{
                            m_data.push('0.00')
                        }
                        conf2.push(m_data);
                        console.log(employeeindex,'==1111=',it,name_,'??????????',moment().format('HH:mm:ss'))
                    }
                } 
            } 
        } else {
            console.log('对不起,你不是主管!无权限查看报表数据!!!!!!!!!!!!!');
            conf2.push([])
        }
    }
    // let buffer = xlsx.build([{name:`门店拜访进度`, data:conf2 }]);
    // fs.writeFileSync(`门店拜访进度.xlsx`,buffer)
    // console.log("----------门店拜访进度导出excle-------------");
    ctx.body = conf2;
    ctx.status = 200
    await next();
})  

//该接口是根据领导id获取其名下员工对应的客户所在城市
router.post('/geCityNameByLeader', async (ctx: IRouterContext, next: any) => {
    let rq: any = ctx.request;
    let res = rq.body;
    let data = await syncDataService.getCityNameBelongToLeader(res.userId);
    if (data.length != 0) {
        ctx.body = data;
        ctx.status = 200
    } else {
        ctx.body = [];
    }
    await next()
}); 

//导出拜访记录------多个人一个月的拜访记录
router.post('/exportVisitRecoderExcel', async (ctx: IRouterContext, next) => {
    let rp: any = ctx.request;
    let res = rp.body;
    console.log(res,'res_res_res_res_res_')
    let month = res.month;
    if (month == undefined || month == null || month == '') {
          month = moment().format('MM');
    }
    let conf: any = [];
    let arr=await emplModel.distinct('userId',{})
    let data5=await emplModel.find({})
    //列
    conf.push(['员工id', '员工名称', '员工工号', '月份', '拜访日期', '拜访类型', '拜访记录id',
        '编码', '名称', '时间戳', '客户类型', '拜访方式', '拜访目的', '定位签到', '图片', '洽谈人',
        '洽谈人职位', '联系方式', '拜访结果', '跟进事宜', '合作意向', '样机型号', '样机图片',
        '陈列是否达标', '竞品单品', '店内活动', '店内活动照片', '陈列函', '门店类型', '是否集中陈列',
        "集中陈列照片", '是否陈列几节以上整柜', '整柜照片', '新品型号', '新品照片',
        '店招形象照片', '品牌专区形象照片', '画面形象照片', 'POSM形象照片',
        '门店类型2', 'SKU照片', "鼠标型号", '鼠标数量', '鼠标照片', '键盘型号', '品牌专区', '品牌专区照片'
    ])
    let leaderId = res.leaderId;//领导id
    let access_token=await dTalkApiUtil.getAccessToken();
    let leaderInfo = await dTalkApi.getUserDetail(leaderId,access_token);
    let leaderData=await formDataService.getAuthrity(leaderId,access_token)
    let leaderDept = leaderData.leaderDepts
    let empList:any[]=[]
    if(leaderDept.length>0){
        let sonData=await formDataService.getsonDept(leaderDept,true,access_token,arr)
        empList=await formDataService.newArray(sonData.userIdData)
    }else {
        empList.push(leaderId)
    }
    if (empList.length > 0) {
        for (let empindex = 0; empindex < empList.length; empindex++) {
            let employeeId = empList[empindex];
            if(!(arr.includes(employeeId))){
                continue;
            }
            let name:any='';
            let jobnumber:any='';
            let data9=await emplModel.findOne({userId:employeeId,'$and':[{user:{$ne:''}},{user:{$ne:null}},{jobnumber:{$ne:''}},{jobnumber:{$ne:null}}]})
            if(data9){
                name=data9.userName
                jobnumber=data9.jobnumber
            }else{
                let empData = await dTalkApi.getUserDetail(employeeId,access_token);
                if(empData&&empData.name){
                    name=empData.name
                }
                if(empData&&empData.jobnumber){
                    jobnumber=empData.jobnumber
                }
            }
            let one_data = await visitMonthPlanModel.find({ userId: employeeId, month: month, planDate: { $ne: '' }, "$or": [{ comment: 1, planStatus: { $ne: '同意' } }, { comment: 2, planStatus: '申请取消', cancelApply: '否' }, { comment: 2, planStatus: { $ne: '同意' }, cancelApply: '是' }, { planStatus: '写好拜访记录' }, { planStatus: '未写拜访记录' }] }).sort({ planDate: 1 });
            for (let index = 0; index < one_data.length; index++) {
                let a = one_data[index];
                let m_data: any = [];
                m_data.push(employeeId)     //员工id
                m_data.push(name)   //员工名称
                m_data.push(jobnumber)  //员工工号
                m_data.push(month)  //月份
                m_data.push(a.planDate) //拜访日期
                m_data.push(a.plantype) //拜访类型
                m_data.push(a.visitRecoder) //拜访记录的id
                m_data.push(a.consumerCode) //编码
                m_data.push(a.consumerName) //名称
                if (a.visitRecoder) {
                    if (a.plantype != '门店') {
                        let b = await consumerRecoderModel.findById({ _id: a.visitRecoder });
                        if(b){
                            m_data.push(b.timestamp)//时间戳
                            m_data.push(b.consumerType)//客户类型
                            m_data.push(b.vistitWay)//拜访方式
                            m_data.push(b.purpose)//拜访目的
                            m_data.push(b.position)//定位签到
                            let c_pic=await formDataService.getPicURLFromString(b.picture)
                            m_data.push(c_pic)//图片
                            m_data.push(b.talkingPerson)//洽谈人
                            m_data.push(b.job)//洽谈人职位
                            m_data.push(b.connection)//联系方式
                            m_data.push(b.visitResult)//拜访结果
                            m_data.push(b.followMatter)//跟进事宜
                            m_data.push(b.cooperation)//合作意向
                        }else{
                            m_data.push('')//时间戳
                            m_data.push('')//客户类型
                            m_data.push('')//拜访方式
                            m_data.push('')//拜访目的
                            m_data.push('')//定位签到
                            m_data.push('')//图片
                            m_data.push('')//洽谈人
                            m_data.push('')//洽谈人职位
                            m_data.push('')//联系方式
                            m_data.push('')//拜访结果
                            m_data.push('')//跟进事宜
                            m_data.push('')//合作意向
                        } 
                    } else {
                        let b=await storeRecoderModel.findById({_id:a.visitRecoder})
                        if(b){
                            let s: any = '';//门店陈列是否达标
                            if (b&&b.isListStandard) {
                                s = '达标';
                            } else {
                                s = '不达标';
                            }
                            let c: any = '';//是否集中陈列
                            let c_list_pic:any=''
                            if (b.storeType == 'A/B店' || b.storeType == 'LFR' || b.storeType == '其他') {
                              let listfoc=b.information[0].isListFocus
                                if(listfoc){
                                    c = '集中陈列';
                                    c_list_pic=await formDataService.getPicURLFromArray(b.information[0].pictureListFocus)
                                }
                            } 
                            let listpart: any = '';
                            if (b.storeType == '模范店') {
                                listpart = '陈列7节以上整柜'
                            } else if (b.storeType == 'Gaming店') {
                                listpart = '陈列3节以上整柜'
                            } else if (b.storeType == '时尚潮品店/智能馆/音乐店') {
                                listpart = '陈列2节以上整柜'
                            }
                            let d: any = '';
                            if (b.storeType == '时尚潮品店/智能馆/音乐店') {
                                d = b.information[0].storeTypes;
                            } else {
                                d = '';
                            }
                            m_data.push(b.timestamp)//时间戳
                            m_data.push('')//客户类型
                            m_data.push('')//拜访方式
                            m_data.push('')//拜访目的
                            m_data.push(b.position)//定位签到
                            let c_pic=await formDataService.getPicURLFromString(b.picture)
                            m_data.push(c_pic)//门头照片
                            m_data.push('')//洽谈人
                            m_data.push('')//洽谈人职位
                            m_data.push('')//联系方式
                            m_data.push('')//拜访结果
                            m_data.push('')//跟进事宜
                            m_data.push('')//合作意向
                            let sampleList = b.sampleList;//样机列表
                            if (sampleList.length > 0) {
                                for (let samindex = 0; samindex < sampleList.length; samindex++) {
                                    let sam = sampleList[samindex];
                                    m_data.push(sam.sampleType)//样机型号
                                    let sam_pic=await formDataService.getPicURLFromArray(sam.Picturesample)
                                    m_data.push(sam_pic)   //样机照片    
                                }
                            } else {
                                m_data.push('');//样机型号
                                m_data.push('');    //样机照片            
                            }
                            m_data.push(s)//门店陈列是否达标
                            m_data.push(b.competitor)//竞品单品
                            m_data.push(b.storeActivity)//店内活动
                            let act_pic=await formDataService.getPicURLFromArray(b.pictureStoreActivity)
                            m_data.push(act_pic) //店内活动照片
                            let c_letter=await formDataService.getPicURLFromString(b.listLetter)
                            m_data.push(c_letter)//陈列函
                            m_data.push(b.storeType)//门店类型
                            m_data.push(c)//是否集中陈列
                            m_data.push(c_list_pic)//集中陈列照片
                            m_data.push(listpart)//是否陈列几节以上整柜
                            if (b.information.length > 0) {
                                let full_pic=await formDataService.getPicURLFromArray(b.information[0].pictureFullContainer)
                                m_data.push(full_pic)//整柜照片
                                let newProductList = b.information[0].newProductList;//新品上架
                                if (newProductList.length > 0) {
                                    for (let newkey = 0; newkey < newProductList.length; newkey++) {
                                        let sam = newProductList[newkey];
                                        m_data.push(sam.type)//新品型号
                                        let new_pro_pic=await formDataService.getPicURLFromArray(sam.pictureNewProduct)
                                        m_data.push(new_pro_pic)    //新品照片   
                                    }
                                } else {
                                    m_data.push('');//新品型号
                                    m_data.push('');   //新品照片            
                                }
                                let pic_sto_fig=await formDataService.getPicURLFromArray(b.information[0].pictureStoreFigure)
                                m_data.push(pic_sto_fig)//店招形象照片
                                let pic_brand=await formDataService.getPicURLFromArray(b.information[0].brandPicture)
                                m_data.push(pic_brand)//品牌专区形象照片
                                let pic_frame=await formDataService.getPicURLFromArray(b.information[0].pictureFrame)
                                m_data.push(pic_frame)//画面形象照片
                                let pic_posm=await formDataService.getPicURLFromArray(b.information[0].picturePosm)
                                m_data.push(pic_posm)//POSM形象照片
                                m_data.push(d)//门店类型2
                                let pic_suk=await formDataService.getPicURLFromArray(b.information[0].pictureSUK)
                                m_data.push(pic_suk)//SKU照片
                                let mouseList = b.information[0].mouseList;//鼠标列表
                                if (mouseList.length > 0) {
                                    for (let moukey = 0; moukey < mouseList.length; moukey++) {
                                        let smou = mouseList[moukey];
                                        m_data.push(smou.mousetype)//鼠标型号
                                        m_data.push(smou.mouseCount)  //鼠标数量   
                                        let pic_mou=await formDataService.getPicURLFromArray(smou.mousePicture)  
                                        m_data.push(pic_mou)   //鼠标照片   
                                    }
                                } else {
                                    m_data.push('');//鼠标型号
                                    m_data.push('');   //鼠标数量     
                                    m_data.push('');    //鼠标照片               
                                }
                                m_data.push(b.information[0].keyBoardTtype)//键盘型号
                                m_data.push(b.information[0].brand)//品牌专区
                                let pictureBrand=await formDataService.getPicURLFromArray(b.information[0].pictureBrand)
                                m_data.push(pictureBrand)//品牌专区照片
                            } else {
                                m_data.push('')
                            }
                        }else{
                            m_data.push('')//时间戳
                            m_data.push('')//客户类型
                            m_data.push('')//拜访方式
                            m_data.push('')//拜访目的
                            m_data.push('')//定位签到
                            m_data.push('')//门头照片
                            m_data.push('')//洽谈人
                            m_data.push('')//洽谈人职位
                            m_data.push('')//联系方式
                            m_data.push('')//拜访结果
                            m_data.push('')//跟进事宜
                            m_data.push('')//合作意向
                            m_data.push('');//样机型号
                            m_data.push('');    //样机照片            
                            m_data.push('')//门店陈列是否达标
                            m_data.push('')//竞品单品
                            m_data.push('')//店内活动
                            m_data.push('') //店内活动照片
                            m_data.push('')//陈列函
                            m_data.push('')//门店类型
                            m_data.push('')//是否集中陈列
                            m_data.push('')//集中陈列照片
                            m_data.push('')//是否陈列几节以上整柜
                            m_data.push('')//整柜照片
                            m_data.push('');//新品型号
                            m_data.push('');   //新品照片       
                            m_data.push('')//店招形象照片
                            m_data.push('')//品牌专区形象照片
                            m_data.push('')//画面形象照片
                            m_data.push('')//POSM形象照片
                            m_data.push('')//门店类型2
                            m_data.push('')//SKU照片                              
                            m_data.push('');//鼠标型号
                            m_data.push('');   //鼠标数量     
                            m_data.push('');    //鼠标照片   
                            m_data.push('')//键盘型号
                            m_data.push('')//品牌专区
                            m_data.push('')//品牌专区照片                               
                        }
                    }
                }
                conf.push(m_data)
            }
        }
    }
    // let buffer=xlsx.build([{name: `${month}月份员工的拜访记录表`,data:conf}]);
    // fs.writeFileSync(`${month}月份员工的拜访记录表.xlsx`,buffer);
    // console.log("----------员工拜访记录导出excle-------------");
    ctx.body = conf;
    ctx.status = 200
    await next();
})


/* 
//导出empl
router.post('/export2', async (ctx: IRouterContext, next) => {
    let data = await emplModel.find({});
    let conf: any = [];
    //列
    conf.push(['userId', '姓名', '部门'])
    //行
    for (let index = 0; index < data.length; index++) {
        let a = data[index];
        console.log(a,'aaaaaaaaaaaa',index)
        conf.push([
            a.userId,
            a.userName,
            a.deptId
        ])
    }
    let buffer = xlsx.build([{ name: `罗技人员信息表`, data: conf }]);
     console.log(buffer,'buffer_buffer_buffer_buffer_buffer_buffer_buffer_buffer_buffer_')
    fs.writeFileSync(`罗技人员信息表.xlsx`, buffer);
    console.log("----------罗技人员信息导出excle-------------");
    ctx.body = { ok: 1 };
    ctx.status = 200
    await next();
}) */

/* //备用
router.post('/exportStoreExcel2', async (ctx: IRouterContext, next) => {
    let data = await storeInfoModel.find({});
    let conf: any = [];
    //列
    conf.push(['门店编码', '门店名称', '渠道', '城市等级', '门店等级',
        '联系人姓名', '联系人电话', '客户编号', '客户名称',
        '门店城市', '门店地址', '电脑城名称', '电脑城地址'
    ])
    //行
    for (let index = 0; index < data.length; index++) {
        let a = data[index];
        conf.push([
            a.storeCode,
            a.storeName,
            a.channel,
            a.cityType,
            a.store_classification,//门店等级
            a.contact_name,
            a.consumerCode,
            a.consumerName,
            a.city,
            a.store_address,
            a.iT_Mall_name,
            a.iT_Mall_address
        ])
    }
    let buffer = xlsx.build([{ name: `门店信息表`, data: conf }]);
    // console.log(buffer,'buffer_buffer_buffer_buffer_buffer_buffer_buffer_buffer_buffer_')
    // fs.writeFileSync(`门店信息表.xlsx`, buffer);
    // console.log("----------门店信息导出excle-------------");
    ctx.body = { ok: 1 };
    ctx.status = 200
    await next();
}) */

/* //导出拜访记录------多个人一个月的拜访记录
router.post('/exportVisitRecoderExcel2', async (ctx: IRouterContext, next) => {
    let rp: any = ctx.request;
    let res = rp.body;
    let month = res.month;
    if (month == undefined || month == null || month == '') {
        month = moment().format('MM');
    }
    let conf: any = [];
    //列
    conf.push(['员工id', '员工名称', '员工工号', '月份', '拜访日期', '拜访类型', '拜访记录id',
        '编码', '名称', '时间戳', '客户类型', '拜访方式', '拜访目的', '定位签到', '图片', '洽谈人',
        '洽谈人职位', '联系方式', '拜访结果', '跟进事宜', '合作意向', '样机型号', '样机图片',
        '陈列是否达标', '竞品单品', '店内活动', '店内活动照片', '陈列函', '门店类型', '是否集中陈列',
        "集中陈列照片", '是否陈列几节以上整柜', '整柜照片', '新品型号', '新品照片',
        '店招形象照片', '品牌专区形象照片', '画面形象照片', 'POSM形象照片',
        '门店类型2', 'SKU照片', "鼠标型号", '鼠标数量', '鼠标照片', '键盘型号', '品牌专区', '品牌专区照片'
    ])
    let leaderId = res.leaderId;//领导id
    let access_token=await dTalkApiUtil.getAccessToken();
    let leaderInfo = await dTalkApi.getUserDetail(leaderId,access_token);
    let leaderData=await formDataService.getAuthrity(leaderId,access_token)
    let leaderDept = leaderData.leaderDepts
    let arr:any=[]
    let data5=await emplModel.find({})
    if(data5.length>0){
        for (let index5 = 0; index5 < data5.length; index5++) {
            let element = data5[index5];
            arr.push(element.userId)
        }
    }
    
    if(leaderDept.length>0){
        let sonData=await formDataService.getsonDept(leaderDept,true,access_token)
        let empList2=await formDataService.newArray(sonData.userIdData)
        let empList:any[]=[];
        for (let index = 0; index < empList2.length; index++) {
            let element = empList2[index];
            if(arr.includes(element)){
                empList.push(element)
            }
        }
        if (empList.length > 0) {
            for (let empindex = 0; empindex < empList.length; empindex++) {
                let id = empList[empindex];
                let employeeId = id;
               /*  if(!(arr.includes(employeeId))){
                    continue;
                } */
                // console.log('导出'+ month + '月份的拜访记录!!!!!!!!!!!!!!');
               /* let empData = await dTalkApi.getUserDetail(employeeId,access_token);
                let one_data = await visitMonthPlanModel.find({ userId: employeeId, month: month, planDate: { $ne: '' }, "$or": [{ comment: 1, planStatus: { $ne: '同意' } }, { comment: 2, planStatus: '申请取消', cancelApply: '否' }, { comment: 2, planStatus: { $ne: '同意' }, cancelApply: '是' }, { planStatus: '写好拜访记录' }, { planStatus: '未写拜访记录' }] }).sort({ planDate: 1 });
                for (let index = 0; index < one_data.length; index++) {
                    let a = one_data[index];
                    let m_data: any = [];
                    m_data.push(employeeId)     //员工id
                    m_data.push(empData.name)   //员工名称
                    m_data.push(empData.jobnumber)  //员工工号
                    m_data.push(month)  //月份
                    m_data.push(a.planDate) //拜访日期
                    m_data.push(a.plantype) //拜访类型
                    m_data.push(a.visitRecoder) //拜访记录的id
                    m_data.push(a.consumerCode) //编码
                    m_data.push(a.consumerName) //名称
                
                    if (a.visitRecoder) {
                        if (a.plantype != '门店') {
                            let b = await consumerRecoderModel.findById({ _id: a.visitRecoder });
                            m_data.push(b.timestamp)//时间戳
                            m_data.push(b.consumerType)//客户类型
                            m_data.push(b.vistitWay)//拜访方式
                            m_data.push(b.purpose)//拜访目的
                            m_data.push(b.position)//定位签到
                            let c_pic=await formDataService.getPicURLFromString(b.picture)

                            m_data.push(c_pic)//图片
                            m_data.push(b.talkingPerson)//洽谈人
                            m_data.push(b.job)//洽谈人职位
                            m_data.push(b.connection)//联系方式
                            m_data.push(b.visitResult)//拜访结果
                            m_data.push(b.followMatter)//跟进事宜
                            m_data.push(b.cooperation)//合作意向
                        } else {
                          
                            let b = await storeRecoderModel.findById({ _id: a.visitRecoder });
                            let s: any = '';//门店陈列是否达标
                            if (b.isListStandard) {
                                s = '达标';
                            } else {
                                s = '不达标';
                            }
                            let c: any = '';//是否集中陈列
                            let c_list_pic:any=''
                            if (b.storeType == 'A/B店' || b.storeType == 'LFR' || b.storeType == '其他') {
                              let listfoc=b.information[0].isListFocus
                                if(listfoc){
                                    c = '集中陈列';
                                    c_list_pic=await formDataService.getPicURLFromArray(b.information[0].pictureListFocus)
                                    
                                }
                               
                            } 
                            let listpart: any = '';
                            if (b.storeType == '模范店') {
                                listpart = '陈列7节以上整柜'
                            } else if (b.storeType == 'Gaming店') {
                                listpart = '陈列3节以上整柜'
                            } else if (b.storeType == '时尚潮品店/智能馆/音乐店') {
                                listpart = '陈列2节以上整柜'
                            }
                            let d: any = '';
                            if (b.storeType == '时尚潮品店/智能馆/音乐店') {
                                d = b.information[0].storeTypes;
                            } else {
                                d = '';
                            }
                            m_data.push(b.timestamp)//时间戳
                            m_data.push('')//客户类型
                            m_data.push('')//拜访方式
                            m_data.push('')//拜访目的
                            m_data.push(b.position)//定位签到
                            let c_pic=await formDataService.getPicURLFromString(b.picture)

                            m_data.push(c_pic)//门头照片
                            m_data.push('')//洽谈人
                            m_data.push('')//洽谈人职位
                            m_data.push('')//联系方式
                            m_data.push('')//拜访结果
                            m_data.push('')//跟进事宜
                            m_data.push('')//合作意向
                            let sampleList = b.sampleList;//样机列表
                            if (sampleList.length > 0) {
                                for (let samindex = 0; samindex < sampleList.length; samindex++) {
                                    let sam = sampleList[samindex];
                                    m_data.push(sam.sampleType)//样机型号
                                    let sam_pic=await formDataService.getPicURLFromArray(sam.Picturesample)
                                    m_data.push(sam_pic)   //样机照片    
                                }
                            } else {
                                m_data.push('');//样机型号
                                m_data.push('');    //样机照片            
                            }
                         
                            m_data.push(s)//门店陈列是否达标
                            m_data.push(b.competitor)//竞品单品
                            m_data.push(b.storeActivity)//店内活动
                            let act_pic=await formDataService.getPicURLFromArray(b.pictureStoreActivity)
                            
                            m_data.push(act_pic) //店内活动照片
                            let c_letter=await formDataService.getPicURLFromString(b.listLetter)
                            m_data.push(c_letter)//陈列函
                            m_data.push(b.storeType)//门店类型

                            m_data.push(c)//是否集中陈列
                            m_data.push(c_list_pic)//集中陈列照片
                            m_data.push(listpart)//是否陈列几节以上整柜
                            if (b.information.length > 0) {
                                let full_pic=await formDataService.getPicURLFromArray(b.information[0].pictureFullContainer)
                                m_data.push(full_pic)//整柜照片
                                let newProductList = b.information[0].newProductList;//新品上架
                                if (newProductList.length > 0) {
                                    for (let newkey = 0; newkey < newProductList.length; newkey++) {
                                        let sam = newProductList[newkey];
                                        m_data.push(sam.type)//新品型号
                                        let new_pro_pic=await formDataService.getPicURLFromArray(sam.pictureNewProduct)
                                        m_data.push(new_pro_pic)    //新品照片   
                                    }
                                } else {
                                    m_data.push('');//新品型号
                                    m_data.push('');   //新品照片            
                                }
                                let pic_sto_fig=await formDataService.getPicURLFromArray(b.information[0].pictureStoreFigure)
                                m_data.push(pic_sto_fig)//店招形象照片

                                let pic_brand=await formDataService.getPicURLFromArray(b.information[0].brandPicture)
                                m_data.push(pic_brand)//品牌专区形象照片

                                let pic_frame=await formDataService.getPicURLFromArray(b.information[0].pictureFrame)
                                m_data.push(pic_frame)//画面形象照片

                                let pic_posm=await formDataService.getPicURLFromArray(b.information[0].picturePosm)
                                m_data.push(pic_posm)//POSM形象照片

                                m_data.push(d)//门店类型2
                                let pic_suk=await formDataService.getPicURLFromArray(b.information[0].pictureSUK)
                                m_data.push(pic_suk)//SKU照片
                                let mouseList = b.information[0].mouseList;//鼠标列表
                                if (mouseList.length > 0) {
                                    for (let moukey = 0; moukey < mouseList.length; moukey++) {
                                        let smou = mouseList[moukey];
                                        m_data.push(smou.mousetype)//鼠标型号
                                        m_data.push(smou.mouseCount)  //鼠标数量   
                                        let pic_mou=await formDataService.getPicURLFromArray(smou.mousePicture)  
                                        m_data.push(pic_mou)   //鼠标照片   
                                    }
                                   
                                } else {
                                    m_data.push('');//鼠标型号
                                    m_data.push('');   //鼠标数量     
                                    m_data.push('');    //鼠标照片               
                                }
                                m_data.push(b.information[0].keyBoardTtype)//键盘型号
                                m_data.push(b.information[0].brand)//品牌专区
                                let pictureBrand=await formDataService.getPicURLFromArray(b.information[0].pictureBrand)
                                m_data.push(pictureBrand)//品牌专区照片
                            } else {
                                m_data.push('')
                            }
                        }
                    }
                    conf.push(m_data)
                }
            }
        }
    }else {
        console.log('对不起,你不是主管!无权限么查看数据!!!!!!!!!!!!!!!!')
        conf.push('')
    }
   
    // let buffer=xlsx.build([{name: `${month}月份员工的拜访记录表`,data:conf}]);
    // fs.writeFileSync(`${month}月份员工的拜访记录表.xlsx`,buffer);
    // console.log("----------员工拜访记录导出excle-------------");
    // ctx.body = { ok: 1 };
    ctx.body = conf;
    ctx.status = 200
    await next();
}) */





export default router;